-- @owner: @zou_jialiang050
-- @date: 2022/02/24
-- @testpoint: 在src视图上删除元素时，删除src表中id1=1的行，插入元素时同步插入至src表，更新元素时，更新src表id3=100

--step1:建表;expect:成功
drop table if exists t_trigger_src_00068 cascade;
create table t_trigger_src_00068(id1 int, id2 int, id3 int) with (storage_type=ustore);

--step2:在src视图上删除元素时，删除src表中id1=1的行，插入元素时同步插入至src表，更新元素时，更新src表id3=100 each row;expect:成功
create or replace view t_trigger_src_00068_view1 as select * from t_trigger_src_00068;
create or replace function insert_func()
returns trigger as $$
begin
if(tg_op='DELETE')
then
delete from t_trigger_src_00068 where id1=1;
return new;
end if;
if(tg_op='INSERT')
then
insert into t_trigger_src_00068 values(new.id1,new.id2,new.id3);
return new;
end if;
if(tg_op='UPDATE')
then
update t_trigger_src_00068 set id3=100;
return new;
end if;
return null;
end;
$$language plpgsql;
/
drop trigger if exists insert_trigger_1 on t_trigger_src_00068_view1;
create trigger insert_trigger_1
instead of delete or insert or update on t_trigger_src_00068_view1
for each row
execute procedure insert_func();
/
insert into t_trigger_src_00068 values(1,1,1),(2,2,2),(3,3,3);
select * from t_trigger_src_00068_view1 order by id1;
delete from t_trigger_src_00068_view1 where id1=2;
select * from t_trigger_src_00068_view1 order by id1;
insert into t_trigger_src_00068_view1 values(4,4,4);
select * from t_trigger_src_00068_view1 order by id1;
update t_trigger_src_00068_view1 set id3=200;
select * from t_trigger_src_00068_view1 order by id1;

--step3:清理环境;expect:成功
drop trigger if exists insert_trigger_1 on t_trigger_src_00068_view1;
drop table if exists t_trigger_src_00068 cascade;
drop view if exists t_trigger_src_00068_view1;
drop function if exists insert_func;
